--    Author    : ChenErHao
--    Name      : ADM.RPT_LOAF_DETAIL.HQL
--    Functions : 固定报表_贷款发生额明细
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-17  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.RPT_LOAF_DETAIL PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 

-- 贷款发放额
 SELECT
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                               -- 贷款余额
   ,'A01' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE  DATA_DATE ='#V_DATA_DATE#'
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE

UNION ALL
 -- 一、短期贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A02' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#'
       AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                      -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE

UNION ALL
 -- 　（二）个人贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A06' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                             -- 行业 = 1
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                         -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　　　　1.个人经营性贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A07' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                              -- 行业 = 1
   AND PRODUCT_CATEGORY='F022'                                                   -- 贷款产品类别 = F022
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                          -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　　　　2.个人消费贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A08' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                              -- 行业 = 1
   AND PRODUCT_CATEGORY IN ('F0211','F0212','F02131','F02132','F0219')           -- 贷款产品类别
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                          -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　（三）单位贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A09' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB<>'2'                                                             -- 行业不等于 2
   AND CUSTOMER_TYPE ='0'                                                        -- 客户类型为 0
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                           -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
--　　　　1.单位经营贷款发放额
--　　　　2.单位固定资产贷款发放额
--　　　　3.单位并购贷款发放额
--　　　　4.贸易融资发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN PRODUCT_CATEGORY='F022' THEN 'A10'
         WHEN PRODUCT_CATEGORY='F023' THEN 'A11'
         WHEN PRODUCT_CATEGORY='F12' THEN 'A12'
         WHEN PRODUCT_CATEGORY='F081' THEN 'A13'
         WHEN PRODUCT_CATEGORY='F082' THEN 'A13'
       ELSE ''
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB<>'2'                                                             -- 行业不等于2
   AND CUSTOMER_TYPE ='0'                                                        -- 客户类型为0
   AND PRODUCT_CATEGORY IN ('F022','F023','F12','F081','F082')                   -- 贷款产品类型
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                          -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,PRODUCT_CATEGORY
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　（四）对非居民贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A14' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='2'                                                              -- 行业类别为2
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) <= 12                          -- 贷款到期日期-贷款发放日期 <= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 二、中长期贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A15' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND PRODUCT_CATEGORY <> 'F09'                                                 -- 贷款产品类别不为 F09
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　（一）个人贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A16' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                             -- 行业 = 1
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                         -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE

UNION ALL
 -- 　　　　1.个人经营性贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A17' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                              -- 行业 = 1
   AND PRODUCT_CATEGORY='F022'                                                   -- 贷款产品类别 = F022
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　　　　2.个人消费贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A18' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='1'                                                              -- 行业 = 1
   AND PRODUCT_CATEGORY IN ('F0211','F0212','F02131','F02132','F0219')           -- 贷款产品类别
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE

UNION ALL

 -- 　（二）单位贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A19' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB<>'2'                                                             -- 行业不等于 2
   AND CUSTOMER_TYPE ='0'                                                        -- 客户类型为 0
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                           -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
--　　　　1.单位经营贷款发放额
--　　　　2.单位固定资产贷款发放额
--　　　　3.单位并购贷款发放额
--　　　　4.贸易融资发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN PRODUCT_CATEGORY='F022' THEN 'A20'
         WHEN PRODUCT_CATEGORY='F023' THEN 'A21'
         WHEN PRODUCT_CATEGORY='F12'  THEN 'A22'
         WHEN PRODUCT_CATEGORY='F081' THEN 'A23'
         WHEN PRODUCT_CATEGORY='F082' THEN 'A23'
       ELSE ''
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB<>'2'                                                             -- 行业不等于2
   AND CUSTOMER_TYPE ='0'                                                        -- 客户类型为0
   AND PRODUCT_CATEGORY IN ('F022','F023','F12','F081','F082')                   -- 贷款产品类型
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,PRODUCT_CATEGORY
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 　（三）对非居民贷款发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A24' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB='2'                                                              -- 行业类别为2
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL
 -- 三、融资租赁发放额
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                          -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_OCCURRENCE) AS AMOUNT                                                  -- 贷款余额
   ,'A25' AS SUBJECT_SEQ                                                         -- 指标序号
   ,LOAN_GRANT_RECOVER AS FLAG                                                   -- 发放回收标识 1-发放,2-回收
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAF_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
   AND LPIC_SUB <> '2'                                                           -- 行业类别不为2
   AND CUSTOMER_TYPE ='0'                                                        -- 客户类型为0
   AND PRODUCT_CATEGORY ='F09'                                                   -- 贷款产品类别为 F09
   AND MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE) >= 12                          -- 贷款到期日期-贷款发放日期 >= 12月
 GROUP BY 
   LOAN_GRANT_RECOVER
  ,DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
;